package com.polelink.testcenter.service.impl;

import com.polelink.testcenter.dto.*;
import com.polelink.testcenter.exception.ExcelParseException;
import com.polelink.testcenter.service.ExcelParserService;
import com.polelink.testcenter.util.ExcelUtils;
import com.polelink.testcenter.util.IniWriter;
import jakarta.annotation.Resource;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.math3.util.Pair;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
@Slf4j
@RequiredArgsConstructor
public class ExcelParserServiceImpl implements ExcelParserService {

    @Resource
    private final ExcelUtils excelUtils;
    @Resource
    private final IniWriter iniWriter;

    @Override
    public List<EcuInfoDto> parseEcuInfo(Sheet sheet) throws ExcelParseException {
        try {
            List<EcuInfoDto> ecuInfos = new ArrayList<>();
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                EcuInfoDto dto = new EcuInfoDto(
                        (int) excelUtils.getNumericCellValue(row.getCell(0)),
                        excelUtils.getStringCellValue(row.getCell(1)),
                        excelUtils.getStringCellValue(row.getCell(2)),
                        excelUtils.getStringCellValue(row.getCell(3)),
                        excelUtils.getStringCellValue(row.getCell(4)),
                        excelUtils.getStringCellValue(row.getCell(5)),
                        excelUtils.getStringCellValue(row.getCell(6))
                );
                //ECU，Physical Diagnostic Request ID（HEX），Logical Address(DoIP)列不允许为空，其余列为空则使用默认值
                if (dto.ecu() == null) {
                    throw new ExcelParseException("ECU信息表中必填字段" + "ECU列" + "缺失，行号: " + (i + 1));
                }
                if (dto.diagnosticRequestAddress() == null) {
                    throw new ExcelParseException("ECU信息表中必填字段" + "Physical Diagnostic Request ID（HEX）列" + "缺失，行号: " + (i + 1));
                }
                if (dto.logicalAddress() == null) {
                    throw new ExcelParseException("ECU信息表中必填字段" + "Logical Address (DoIP)列" + "缺失，行号: " + (i + 1));
                }

                ecuInfos.add(dto);
            }
            log.info("成功解析ECU信息，共{}条记录", ecuInfos.size());
            return ecuInfos;
        } catch (Exception e) {
            log.error("解析ECU信息表失败", e);
            throw new ExcelParseException("解析ECU信息表失败: " + e.getMessage(), e);
        }
    }

    @Override
    public Pair<VehicleInfoDto, List<EcuConfigDto>> parseEcuConfig(List<EcuInfoDto> ecuInfos, Sheet sheet) throws ExcelParseException {
        try {
            // 新增ECU名称校验准备
            Set<String> validEcuNames = new HashSet<>();
            Set<String> validFbomNames = new HashSet<>();
            // 假设ecuInfos是已解析的ECU信息列表（需要根据实际数据来源调整）
            if (ecuInfos == null || ecuInfos.isEmpty()) {
                throw new ExcelParseException("ECU Info表缺少控制器");
            }
                validEcuNames = ecuInfos.stream().map(EcuInfoDto::ecu).collect(Collectors.toSet());
                validFbomNames = ecuInfos.stream().map(EcuInfoDto::fbom).collect(Collectors.toSet());


            // 解析车辆信息
            // 调整行索引映射关系
            List<String> requiredFields = Arrays.asList("VIN码", "生产日期", "车型规格号", "安装日期");
            Row descRow = sheet.getRow(1);    // 描述行（第2行）
            Row didRow = sheet.getRow(2);      // DID编号行（第3行）
            if (descRow == null) {
                throw new ExcelParseException("ECU Config表缺少描述行（第2行）");
            }

            // 收集实际存在的字段
            Map<Integer, String> actualFields = new LinkedHashMap<>();
            for (int col = 0; col < descRow.getLastCellNum(); col++) {
                String cellValue = excelUtils.getStringCellValue(descRow.getCell(col));
                if (cellValue != null) {
                    actualFields.put(col, cellValue);
                }
            }

            // 校验必填字段
            List<String> missingFields = new ArrayList<>();
            List<String> missingDetails = new ArrayList<>();
            for (String required : requiredFields) {
                if (!actualFields.containsValue(required)) {
                    missingFields.add(required);
                    // 查找缺失字段的列位置
                    String detail = required + "（缺失于第2行，预期应出现在列："
                            + getExpectedColumn(actualFields, required) + "）";
                    missingDetails.add(detail);
                }
            }

            if (!missingFields.isEmpty()) {
                String errorMsg = String.format("ECU Config表描述行校验失败：\n- 缺失字段：%s\n- 详细位置：%s",
                        String.join("、", missingFields),
                        String.join("\n  ", missingDetails));
                throw new ExcelParseException(errorMsg);
            }
            Row valueRow = sheet.getRow(3);    // 数据行（第4行）

            // 新增DID配置解析
            Map<String, DidConfig> didConfigs = new LinkedHashMap<>();
            for (int col = 0; col < descRow.getLastCellNum(); col++) {
                String desc = excelUtils.getStringCellValue(descRow.getCell(col));
                String did = excelUtils.getHexStringCellValue(didRow.getCell(col));
                String data = excelUtils.getHexStringCellValue(valueRow.getCell(col));

                if (desc != null && did != null) {
                    didConfigs.put(desc, new DidConfig(did, data));
                }
            }
            // 构建车辆信息DTO
            VehicleInfoDto vehicleInfo = new VehicleInfoDto(
                    didConfigs.get("VIN码").data(),
                    didConfigs.get("生产日期").data(),
                    didConfigs.get("车型规格号").data(),
                    didConfigs.get("安装日期").data(),
                    didConfigs
            );
            // 验证必填字段
            if (didConfigs.values().stream().anyMatch(c -> c.data() == null)) {
                throw new ExcelParseException("车辆信息中必填DID数据缺失");
            }
            // 验证必填车辆信息
            if (vehicleInfo.vin() == null || vehicleInfo.productionDate() == null ||
                    vehicleInfo.modelSpec() == null || vehicleInfo.installationDate() == null) {
                throw new ExcelParseException("车辆信息中必填字段缺失");
            }

            // 解析ECU配置
            List<EcuConfigDto> ecuConfigs = new ArrayList<>();
            // 新增正则表达式提取括号内的英文缩写
            Pattern pattern = Pattern.compile("\\((.*?)\\)");
            for (int i = 4; i <= sheet.getLastRowNum(); i += 3) {
                Row nameRow = sheet.getRow(i);
                if (nameRow == null) {
                    throw new ExcelParseException("控制器名称行缺失，行号：" + (i + 1));
                }

                // ECU名称强校验
                String ecuName = excelUtils.getStringCellValue(nameRow.getCell(0));
                if (ecuName == null || ecuName.isBlank()) {
                    throw new ExcelParseException("ECU名称不能为空，行号：" + (i + 1));
                }
                // ECU名称有效性校验
                Matcher matcher = pattern.matcher(ecuName);
                String extractedAbbr = null;
                if (matcher.find()) {
                    extractedAbbr = matcher.group(1);
                }
                boolean isValid = validEcuNames.contains(ecuName) || validFbomNames.contains(ecuName)
                        || (extractedAbbr != null && (validEcuNames.contains(extractedAbbr) || validFbomNames.contains(extractedAbbr)));

                if (!isValid) {
                    log.warn("控制器名称'{}'未在ECU信息表中定义，行号：{}", ecuName, i + 1);
                }

                Row ecuDidRow = sheet.getRow(i + 1);
                Row ecuValueRow = sheet.getRow(i + 2);

                // 行存在性校验
                if (ecuDidRow == null || ecuValueRow == null) {
                    throw new ExcelParseException("控制器配置不完整，缺失DID或数据行，行号：" + (i + 1));
                }

                Map<String, String> didValues = new LinkedHashMap<>();
                for (int col = 0; col < ecuDidRow.getLastCellNum(); col++) {
                    // DID强校验
                    String did = excelUtils.getHexStringCellValue(ecuDidRow.getCell(col));
                    if (did == null || did.isBlank()) {
                        throw new ExcelParseException(String.format("DID不能为空 [控制器:%s 列:%d 行号:%d]",
                                ecuName, col + 1, ecuDidRow.getRowNum() + 1));
                    }

                    // 值处理（保持原有NULL处理逻辑）
                    String value = excelUtils.getStringCellValue(ecuValueRow.getCell(col));
                    didValues.put(did, value == null ? "NULL" : value);
                }

                ecuConfigs.add(new EcuConfigDto(ecuName, didValues));
            }

            log.info("成功解析ECU配置，共{}个控制器配置", ecuConfigs.size());
            return Pair.create(vehicleInfo, ecuConfigs);
        } catch (Exception e) {
            log.error("解析ECU配置表失败", e);
            throw new ExcelParseException("解析ECU配置表失败: " + e.getMessage(), e);
        }
    }

    // 辅助方法：生成预期列建议
    private String getExpectedColumn(Map<Integer, String> actualFields, String target) {
        return actualFields.entrySet().stream()
                .filter(e -> e.getValue().contains("日期") || e.getValue().contains("码"))
                .findFirst()
                .map(e -> "第" + (e.getKey() + 1) + "列")
                .orElse("未找到合适列位置");
    }

    @Override
    public List<ProjectItemDto> parsePepsSheet(Sheet sheet) throws ExcelParseException {
        try {
            String workstation = sheet.getSheetName();
            Map<Integer, String> controllerColMap = excelUtils.getControllerColumnMap(sheet);
            Map<String, ProjectItemDto> projectMap = new LinkedHashMap<>();

            String currentProject = null;
            String currentSubProject = null;
            int currentStep = 0;

            for (int rowNum = 4; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                // 解析基础信息列
                String project = excelUtils.getStringCellValue(row.getCell(0));
                String subProject = excelUtils.getStringCellValue(row.getCell(1));
                String stepCell = excelUtils.getStringCellValue(row.getCell(2));
                String sequence = excelUtils.getStringCellValue(row.getCell(3));

                // 项目切换处理
                if (project != null && !project.isBlank()) {
                    currentProject = project;
                    currentSubProject = null;
                    currentStep = 0;
                    projectMap.putIfAbsent(currentProject,
                            new ProjectItemDto(workstation, currentProject, new ArrayList<>()));
                }

                // 子项目处理（'-'表示无子项目）
                if (subProject != null && !subProject.isBlank() && !subProject.equals("-")) {
                    currentSubProject = subProject;
                    currentStep = 0;
                    projectMap.get(project).subProjectItemList().add(new SubProjectItemDto(
                            subProject,
                            new ArrayList<>()
                    ));
                }

                // 步骤验证
                if (stepCell == null || !stepCell.matches("\\d+")) {
                    throw new ExcelParseException("无效的步骤编号，行号：" + (rowNum + 1));
                }
                int step = Integer.parseInt(stepCell);

                // 步骤连续性检查（仅在有效步骤时执行）
                if (currentSubProject != null) {
                    if (step != ++currentStep) {
                        throw new ExcelParseException("步骤编号不连续，行号：" + (rowNum + 1));
                    }
                }

                // 处理控制器列数据
                List<ItemStepDto.ControllerOperation> operations = new ArrayList<>();

                // 先收集所有控制器的操作
                for (Map.Entry<Integer, String> entry : controllerColMap.entrySet()) {
                    int reqCol = entry.getKey();
                    int respCol = reqCol + 1;

                    String request = excelUtils.getStringCellValue(row.getCell(reqCol));
                    String response = excelUtils.getStringCellValue(row.getCell(respCol));

                    // 新增空值校验（请求和响应不能同时为空）
                    if ((request != null && !request.isBlank()) ||
                            (response != null && !response.isBlank())) {
                        operations.add(new ItemStepDto.ControllerOperation(
                                entry.getValue(),
                                request,
                                response
                        ));
                    }
                }

                // 有操作数据时才创建步骤
                if (!operations.isEmpty()) {
                    // 获取或创建子项目
                    SubProjectItemDto tempSubProject = getOrCreateSubProject(
                            projectMap.get(currentProject).subProjectItemList(),
                            currentSubProject != null ? currentSubProject : "-"
                    );

                    // 创建步骤对象
                    tempSubProject.steps().add(new ItemStepDto(
                            step,
                            sequence,
                            operations
                    ));
                } else {
                    log.warn("跳过无请求和响应的步骤 行号:{} 步骤号:{}", rowNum + 1, step);
                }
            }

            return new ArrayList<>(projectMap.values());
        } catch (Exception e) {
            throw new ExcelParseException("解析PEPS工作表失败: " + e.getMessage(), e);
        }
    }

    private SubProjectItemDto getOrCreateSubProject(List<SubProjectItemDto> list, String subProjectName) {
        return list.stream()
                .filter(dto -> dto.subProjectName().equals(subProjectName))
                .findFirst()
                .orElseGet(() -> {
                    SubProjectItemDto newDto = new SubProjectItemDto(subProjectName, new ArrayList<>());
                    list.add(newDto);
                    return newDto;
                });
    }

    @Override
    public String generateIniContent(List<EcuInfoDto> ecuInfos,
                                     Pair<VehicleInfoDto, List<EcuConfigDto>> ecuConfigs,
                                     List<ProjectItemDto> pepsItems) {
        return iniWriter.generateIni(ecuInfos, ecuConfigs, pepsItems);
    }
}